package com.yuanren.em.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanren.em.entity.MemberLevel;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface MemberLevelMapper extends BaseMapper<MemberLevel> {
    
    /**
     * 分页查询会员等级（包含用户数量统计）
     */
    @Select("<script>" +
            "SELECT ml.*, " +
            "COALESCE(user_count.count, 0) as userCount " +
            "FROM t_member_level ml " +
            "LEFT JOIN (" +
            "    SELECT current_level_id, COUNT(*) as count " +
            "    FROM t_user_member " +
            "    GROUP BY current_level_id" +
            ") user_count ON ml.id = user_count.current_level_id " +
            "WHERE ml.is_active = 1 " +
            "<if test='levelName != null and levelName != \"\"'>" +
            "AND ml.level_name LIKE CONCAT('%', #{levelName}, '%') " +
            "</if>" +
            "<if test='levelCode != null and levelCode != \"\"'>" +
            "AND ml.level_code = #{levelCode} " +
            "</if>" +
            "ORDER BY ml.sort_order ASC, ml.min_points ASC" +
            "</script>")
    IPage<MemberLevel> selectLevelPageWithUserCount(IPage<MemberLevel> page, 
                                                   @Param("levelName") String levelName,
                                                   @Param("levelCode") String levelCode);
    
    /**
     * 根据积分获取对应的会员等级
     */
    @Select("SELECT * FROM t_member_level " +
            "WHERE is_active = 1 " +
            "AND min_points <= #{points} " +
            "AND (max_points IS NULL OR max_points >= #{points}) " +
            "ORDER BY min_points DESC " +
            "LIMIT 1")
    MemberLevel selectLevelByPoints(@Param("points") Integer points);
    
    /**
     * 获取下一个等级
     */
    @Select("SELECT * FROM t_member_level " +
            "WHERE is_active = 1 " +
            "AND min_points > #{currentPoints} " +
            "ORDER BY min_points ASC " +
            "LIMIT 1")
    MemberLevel selectNextLevel(@Param("currentPoints") Integer currentPoints);
    
    /**
     * 获取所有启用的等级（按排序）
     */
    @Select("SELECT * FROM t_member_level " +
            "WHERE is_active = 1 " +
            "ORDER BY sort_order ASC, min_points ASC")
    List<MemberLevel> selectActiveLevels();
    
    /**
     * 根据等级代码查询
     */
    @Select("SELECT * FROM t_member_level " +
            "WHERE level_code = #{levelCode} " +
            "AND is_active = 1")
    MemberLevel selectByLevelCode(@Param("levelCode") String levelCode);
    
    /**
     * 更新等级状态
     */
    @Update("UPDATE t_member_level SET is_active = #{isActive} WHERE id = #{id}")
    int updateLevelStatus(@Param("id") Long id, @Param("isActive") Boolean isActive);
    
    /**
     * 获取等级统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as totalLevels, " +
            "COUNT(CASE WHEN is_active = 1 THEN 1 END) as activeLevels, " +
            "COUNT(CASE WHEN is_active = 0 THEN 1 END) as inactiveLevels " +
            "FROM t_member_level")
    LevelStatistics getLevelStatistics();
    
    /**
     * 等级统计信息内部类
     */
    class LevelStatistics {
        private Integer totalLevels;
        private Integer activeLevels;
        private Integer inactiveLevels;
        
        public Integer getTotalLevels() {
            return totalLevels;
        }
        
        public void setTotalLevels(Integer totalLevels) {
            this.totalLevels = totalLevels;
        }
        
        public Integer getActiveLevels() {
            return activeLevels;
        }
        
        public void setActiveLevels(Integer activeLevels) {
            this.activeLevels = activeLevels;
        }
        
        public Integer getInactiveLevels() {
            return inactiveLevels;
        }
        
        public void setInactiveLevels(Integer inactiveLevels) {
            this.inactiveLevels = inactiveLevels;
        }
    }
} 